package com.etone.smartAudit.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.etone.daemon.db.helper.QueryHelper;
import com.etone.smartAudit.domain.*;
import com.etone.smartAudit.dto.ParamDTO;
import com.etone.smartAudit.enums.DataBaseType;
import com.etone.smartAudit.error.CustomParameterizedException;
import com.etone.smartAudit.mapper.AuditExceptionCountReportMapper;
import com.etone.smartAudit.mapper.AuditExceptionMonthMapper;
import com.etone.smartAudit.mapper.CityMapper;
import com.etone.smartAudit.utils.DateUtil;
import com.etone.smartAudit.utils.Excel.ExcelUtil;
import com.etone.smartAudit.utils.ZipUtil;
import com.etone.smartAudit.utils.database.DatabaseUtil;
import com.etone.smartAudit.vo.FileVO;
import com.etone.smartAudit.vo.PagerVO;
import com.etone.smartAudit.vo.auditException.*;
import com.etone.smartAudit.vo.auditExceptionSpecial.AuditExceptionSpecialSimpleVO;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author: 蒋学伟
 * @create: 2020-06-10 14:25:28
 * @Description AuditExceptionService
 */
@Service
@Slf4j
public class AuditExceptionService {

    @Autowired
    private AuditExceptionMonthMapper auditExceptionMonthMapper;

    @Autowired
    private AuditExceptionTypeService auditExceptionTypeService;

    @Autowired
    private AuditExceptionSpecialService auditExceptionSpecialService;

    @Autowired
    private AuditExceptionCountReportService auditExceptionCountReportService;

    @Autowired
    private CityMapper cityMapper;

    @Value("${spring.datasource.database}")
    private String database;

    @Value("${spring.gpds.schema}")
    private String gpSchema;

    @Value("${spring.gpds.database}")
    private String gpDatabase;

    @Value("${spring.gpds.ip}")
    private String gpIp;

    @Value("${spring.gpds.port}")
    private Integer gpPort;

    @Value("${spring.gpds.username}")
    private String gpUsername;

    @Value("${spring.gpds.password}")
    private String gpPassword;

    public AuditExceptionMonth get(Integer id) {
        AuditExceptionMonth auditExceptionMonth = auditExceptionMonthMapper.findById(id);
        if (auditExceptionMonth == null) {
            throw new CustomParameterizedException("审计异常点不存在");
        }
        return auditExceptionMonth;
    }


    /**
     * 批量查找异常量
     *
     * @param ids
     * @return
     */
    public List<AuditExceptionMonth> selectBatchIds(List<Integer> ids) {
        return auditExceptionMonthMapper.selectBatchIds(ids);
    }

    /**
     * 查询最近一个月全省的数据
     *
     * @param areaId
     * @param typeId
     * @return
     */
    public AuditExceptionMonthAreaListVO findLastMonthArea(String areaId, String typeId) {
        //全省记录
        AuditExceptionMonthAreaListVO auditExceptionMonthAreaListVO = auditExceptionMonthMapper.findAreaLastMonth();
        if (auditExceptionMonthAreaListVO == null) {
            auditExceptionMonthAreaListVO = new AuditExceptionMonthAreaListVO();
        }

        //获取市级记录
        List<AuditExceptionMonthAreaListVO> auditExceptionMonthAreaListVOS = auditExceptionMonthMapper.finAreaCityLastMonth(typeId);
        if (!CollectionUtils.isEmpty(auditExceptionMonthAreaListVOS)) {
            auditExceptionMonthAreaListVO.setChildren(auditExceptionMonthAreaListVOS);
            auditExceptionMonthAreaListVO.setCnt(auditExceptionMonthAreaListVOS.stream().mapToInt(auditExceptionMonthAreaListVO1 -> auditExceptionMonthAreaListVO1.getCnt()).sum());
        }
        return auditExceptionMonthAreaListVO;
    }

    /**
     * 查询所有营销类专题
     *
     * @param specialId
     * @return
     */
    public List<AuditExceptionTypeSimpleVO> findTypeSimple(Integer specialId) {
        return auditExceptionMonthMapper.findTypeSimple(specialId);
    }

    /**
     * 查询审计异常月份
     *
     * @param auditExceptionTypeId 审计点id
     * @param page
     * @param pageSize
     * @return
     */
    public PagerVO<List<Integer>> findAuditExceptionMonth(Integer auditExceptionTypeId, int page, int pageSize) {
        String countPeriods = DateUtil.parseDate2String(new Date(), "yyyy第MM期");
        if (auditExceptionTypeId == null) {
            Page<List<Integer>> monthPager = PageHelper.startPage(page, pageSize).doSelectPage(() -> {
                auditExceptionMonthMapper.findAuditExceptionMonth(auditExceptionTypeId,countPeriods);
            });
            return PagerVO.of(monthPager);
        }

        Connection connection = null;
        List<Integer> data = new ArrayList();
        AuditExceptionType auditExceptionType = auditExceptionTypeService.get(auditExceptionTypeId);
        if (auditExceptionType != null) {
            try {
                connection = DatabaseUtil.getConnection(gpIp, DataBaseType.GREENPLUM, gpUsername, gpPassword, gpPort, gpDatabase);
                String sql = "SELECT date FROM " + gpSchema + "." + auditExceptionType.getTableName() + " WHERE 1=1  GROUP BY date order by date DESC";
                log.info("日期sql:{}", sql);
                data = QueryHelper.selectColumns(connection, sql, "date");
            } catch (SQLException | ClassNotFoundException e) {
                e.printStackTrace();
            } finally {
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException sqlException) {
                        sqlException.printStackTrace();
                    }
                }
            }
        }
        return PagerVO.of(page, pageSize, (long) data.size(), data);

    }

    /**
     * 异常列表
     *
     * @param date
     * @param typeId
     * @param specialId
     * @param page
     * @param pageSize
     * @return
     */
    public PagerVO<AuditExceptionMonthVO> list(String date, Integer cityId, Integer typeId, Integer specialId, int page, int pageSize) {
        return auditExceptionMonthVOList(date, cityId, typeId, specialId, page, pageSize);
    }

    /**
     * 审计异常类型列表
     *
     * @param specialId
     * @param typeId
     * @param page
     * @param pageSize
     * @return
     */
    public PagerVO<AuditExceptionTypeVO> findTypes(Integer specialId, Integer typeId, int page, int pageSize) {
//        Page<AuditExceptionTypeVO> auditExceptionTypeSimpleVOPage = PageHelper.startPage(page, pageSize).doSelectPage(() -> {
//            auditExceptionMonthMapper.findTypeList(specialId, typeId);
//        });
        List<AuditExceptionTypeVO> voList = new ArrayList<>();
        List<AuditExceptionType> typeList = new ArrayList<>();
        Connection connection = null;
        //查询全部
        if (specialId == null && typeId == null) {
            PagerVO<AuditExceptionSpecialSimpleVO> pagerVO = auditExceptionSpecialService.listSimple(10025, 1, 1000);
            for (AuditExceptionSpecialSimpleVO simpleVO : pagerVO.getRows()) {
                typeList.addAll(auditExceptionTypeService.getList(simpleVO.getId()));
            }
        }
        //查询一级分类
        else if (specialId != null && typeId == null) {
            typeList.addAll(auditExceptionTypeService.getList(specialId));
        }
        //查询二级分类
        else {
            typeList.add(auditExceptionTypeService.get(typeId));
        }


        for (AuditExceptionType auditExceptionType : typeList) {
            try {
                AuditExceptionSpecial special = auditExceptionSpecialService.getById(auditExceptionType.getSpecialId());
                connection = DatabaseUtil.getConnection(gpIp, DataBaseType.GREENPLUM, gpUsername, gpPassword, gpPort, gpDatabase);
                String sql = "SELECT date,COUNT(DISTINCT city_name) total " +
                        "FROM " + gpSchema + "." + auditExceptionType.getTableName() +
                        " GROUP BY date ORDER BY date DESC LIMIT 1 OFFSET 0";
                List<Map<String, Object>> map = QueryHelper.selectMap(connection, sql);
                if (!CollectionUtils.isEmpty(map)) {
                    voList.add(AuditExceptionTypeVO.builder().date(Integer.valueOf(map.get(0).get("date").toString()))
                            .areaCnt(Integer.valueOf(map.get(0).get("total").toString()))
                            .name(auditExceptionType.getName())
                            .specialId(special.getId())
                            .specialImportance(special.getImportance())
                            .specialName(special.getName())
                            .id(auditExceptionType.getId()).build());
                }
            } catch (SQLException | ClassNotFoundException e) {
                e.printStackTrace();
            } finally {
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        List<AuditExceptionTypeVO> subList = new ArrayList<>();
        if (!CollectionUtils.isEmpty(voList)) {
            subList = voList.subList(voList.size() > (page - 1) * pageSize ? (page - 1) * pageSize : 0,
                    Math.min(voList.size(), (page - 1) * pageSize + pageSize));
        }
        return PagerVO.of(page, pageSize, (long) voList.size(), subList);
    }

    /**
     * 审计异常详情
     *
     * @param typeId
     * @param cityNames
     * @param dates
     * @param params
     * @param pageSize
     * @param page
     * @return
     */
    public AuditExceptionDetailVO getAuditExceptionDetailVO(Integer typeId, List<String> cityNames, List<String> dates, List<ParamDTO> params, int pageSize, int page) {
        if (!CollectionUtils.isEmpty(params)) {
            String jsonString = JSONArray.toJSONString(params);
            System.out.println(jsonString);
        }

        AuditExceptionType auditExceptionType = auditExceptionTypeService.get(typeId);

        //表名
        String tableName = auditExceptionType.getTableName();

        //获取表头信息
        List<AuditExceptionDetailHeadVO> auditExceptionDetailHeadVOS = new ArrayList<>();
        /**
         List<AuditExceptionDetailHeadVO> auditExceptionDetailHeadVOS = auditExceptionMonthMapper.findTableColumn(database, tableName);
         **/
        try {
            Connection connection = DatabaseUtil.getConnection(gpIp, DataBaseType.GREENPLUM, gpUsername, gpPassword, gpPort, gpDatabase);
            String sql = "\n" +
                    "SELECT a.attnum,\n" +
                    "       a.attname AS field,\n" +
                    "       t.typname AS type,\n" +
                    "       a.attlen AS length,\n" +
                    "       a.atttypmod AS lengthvar,\n" +
                    "       a.attnotnull AS notnull,\n" +
                    "       b.description AS comment\n" +
                    "  FROM pg_class c,\n" +
                    "       pg_attribute a\n" +
                    "       LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,\n" +
                    "       pg_type t\n" +
                    " WHERE c.relname = '" + tableName + "'\n" +
                    "       and a.attnum > 0\n" +
                    "       and a.attrelid = c.oid\n" +
                    "       and a.atttypid = t.oid\n" +
                    " ORDER BY a.attnum";
            log.info("查询结构sql:" + sql);
            List<Map<String, Object>> dataMaps = QueryHelper.selectMap(connection, sql);
            for (Map<String, Object> dataMap : dataMaps) {
                String field = String.valueOf(dataMap.get("field"));
                String type = String.valueOf(dataMap.get("type"));
                String comment = String.valueOf(dataMap.get("comment"));
                auditExceptionDetailHeadVOS.add(AuditExceptionDetailHeadVO.builder().column(field).head(comment).dataType(type).build());
            }

            tableName = gpSchema + "." + tableName;
            //获取数据内容

            String where = " WHERE 1=1";
            if (!CollectionUtils.isEmpty(cityNames)) {
                where += " AND city_name in ('" + cityNames.stream().map(cityName -> cityName.replace("市", "")).collect(Collectors.joining("','")) + "')";
            }
            if (!CollectionUtils.isEmpty(dates)) {
                where += " AND date in (" + dates.stream().collect(Collectors.joining(",")) + ")";
            }
            if (!CollectionUtils.isEmpty(params)) {
                for (ParamDTO param : params) {
                    where += " AND " + param.assembleWhere();
                }

            }
            sql = "select count(1) c from " + tableName + " " + where;
            log.info("查询总条数:" + sql);
            Long count = QueryHelper.selectColumn(connection, sql, "c");
            List<Map<String, Object>> maps = new ArrayList<>();
            if (count != 0) {
                sql = "select * from " + tableName + " " + where + " limit " + pageSize + " offset " + ((page - 1) * pageSize);
                log.info("查询记录:" + sql);
                maps = QueryHelper.selectMap(connection, sql);
            }
            return AuditExceptionDetailVO.builder().head(auditExceptionDetailHeadVOS).data(PagerVO.of(page, pageSize, count, maps)).build();
        } catch (BadSqlGrammarException | SQLException | ClassNotFoundException badSqlGrammarException) {
            badSqlGrammarException.printStackTrace();
            throw new CustomParameterizedException("系统异常,请正确查询");
        }


    }

    /**
     * 下载审计异常
     *
     * @param ids
     * @param response
     */
    @Transactional(rollbackFor = Exception.class)
    public void download(List<Integer> ids, HttpServletResponse response) {
        List<AuditExceptionMonth> auditExceptionMonths = auditExceptionMonthMapper.selectBatchIds(ids);

        ZipUtil.downloadFileZip(auditExceptionMonths.stream().map(auditExceptionMonth -> JSON.parseObject(auditExceptionMonth.getLocalFile(), FileVO.class)).collect(Collectors.toList()), "持续审计结果" + System.currentTimeMillis() + ".zip", response);

    }

    /**
     * 创建服务器本地文件
     *
     * @param force 是否强制更新
     */
    public void createLocalFile(boolean force) {
        Calendar calendar = Calendar.getInstance();
        int curHour = calendar.get(Calendar.HOUR_OF_DAY);
        if (!force && curHour >= 6) {
            return;
        }
        log.info("开始生成持续审计文件");
        Page<AuditExceptionMonth> auditExceptionMonthPage = PageHelper.startPage(1, force ? (Integer.MAX_VALUE - 1) : 1).doSelectPage(() -> {
            auditExceptionMonthMapper.findEmptyFileException(force);
        });
        if (!CollectionUtils.isEmpty(auditExceptionMonthPage)) {
            log.info("查询到{}条数据", auditExceptionMonthPage.size());
            for (AuditExceptionMonth auditExceptionMonth : auditExceptionMonthPage) {
                log.info("开始更新{}月{}地市{}", auditExceptionMonth.getDate(), auditExceptionMonth.getCityName(), auditExceptionMonth.getTypeName());
                //获取详情
                AuditExceptionDetailVO auditExceptionDetailVO = getAuditExceptionDetailVO(auditExceptionMonth.getTypeId(), Arrays.asList(auditExceptionMonth.getCityName()), Arrays.asList(auditExceptionMonth.getDate()), null, Integer.MAX_VALUE - 1, 1);
                log.info("查询到{}月{}地市{}数据量:{}条", auditExceptionMonth.getDate(), auditExceptionMonth.getCityName(), auditExceptionMonth.getTypeName(), auditExceptionDetailVO.getDataList().size());
                FileVO fileVO = ExcelUtil.getExcelOutLocalFile(
                        auditExceptionDetailVO.getHeadList(),
                        auditExceptionDetailVO.getDataList(),
                        "auditException/" + auditExceptionMonth.getTypeName() + "/" + auditExceptionMonth.getCityName(), auditExceptionMonth.getCityName() + auditExceptionMonth.getTypeName() + auditExceptionMonth.getDate() + ".xlsx"
                );
                auditExceptionMonthMapper.updateFile(auditExceptionMonth.getId(), JSON.toJSONString(fileVO));
            }

            if (!force) {
                createLocalFile(force);
            }

        }
    }

    /**
     * 查询区域专题数
     *
     * @param areaId 10025广东,0集团
     * @param date   月份yyyyMM
     * @return
     */
    public List<AuditExceptionSpecialAreaListVO> findAreaSpecialTotal(String areaId, Integer date) {
        String countReport = DateUtil.parseDate2String(new Date(), "yyyy第MM期");
        return auditExceptionMonthMapper.findAreaSpecialTotal(areaId, date,countReport);
    }

    /**
     * 查询区域专题详情
     *
     * @param areaId 10025广东,0集团
     * @param cityId 城市id
     * @param date   月份yyyyMM
     * @return
     */
    public List<AuditExceptionSpecialDetailVO> findAreaSpecialDetail(String areaId, String cityId, Integer date) {

        Map<Integer, AuditExceptionSpecialDetailVO> auditExceptionSpecialDetailVOMap = new HashMap<>();
        String countPeriods = DateUtil.parseDate2String(new Date(), "yyyy第MM期");
        List<AuditExceptionMonthVO> areaSpecialDetail = auditExceptionMonthMapper.findAreaSpecialDetail(areaId, cityId, date,countPeriods);
        for (AuditExceptionMonthVO auditExceptionMonthVO : areaSpecialDetail) {
            Integer specialId = auditExceptionMonthVO.getSpecialId();
            Integer cnt = auditExceptionMonthVO.getCnt();
            AuditExceptionSpecialDetailVO auditExceptionSpecialDetailVO;
            if (auditExceptionSpecialDetailVOMap.containsKey(specialId)) {
                auditExceptionSpecialDetailVO = auditExceptionSpecialDetailVOMap.get(specialId);
            } else {
                auditExceptionSpecialDetailVO = AuditExceptionSpecialDetailVO.builder().id(specialId).name(auditExceptionMonthVO.getSpecialName()).cnt(0).auditExceptionMonths(new ArrayList<>()).build();
            }
            auditExceptionSpecialDetailVO.setCnt(auditExceptionSpecialDetailVO.getCnt() + cnt);
            List<AuditExceptionMonthSimpleVO> auditExceptionMonths = auditExceptionSpecialDetailVO.getAuditExceptionMonths();
            auditExceptionMonths.add(AuditExceptionMonthSimpleVO.builder().cnt(cnt).typeName(auditExceptionMonthVO.getTypeName()).id(auditExceptionMonthVO.getId()).build());
            auditExceptionSpecialDetailVOMap.put(specialId, auditExceptionSpecialDetailVO);
        }
        return auditExceptionSpecialDetailVOMap.values().stream().collect(Collectors.toList());
    }

    /**
     * 查询专题top
     *
     * @param top
     * @param areaId
     * @param date
     * @return
     */
    public List<String> findSpecialTop(Integer top, String areaId, Integer date) {
        String countReport = DateUtil.parseDate2String(new Date(), "yyyy第MM期");
        return auditExceptionMonthMapper.findSpecialTop(top, areaId, date,countReport);
    }

    /**
     * 统计报表
     *
     * @param typeId
     * @param actions
     * @return
     */
    public Map<String, Object> statisticalReport(Integer typeId, Map<String, List<ParamDTO>> actions) {
        AuditExceptionType auditExceptionType = auditExceptionTypeService.get(typeId);
        Map<String, Object> result = new HashMap<>();
        String analysisConfig = auditExceptionType.getAnalysisConfig();
        if (!StringUtils.isEmpty(analysisConfig)) {
            JSONObject analysisConfigJson = JSON.parseObject(analysisConfig);
            Connection connection = null;
            if (actions != null) {
                try {
                    connection = DatabaseUtil.getConnection(gpIp, DataBaseType.GREENPLUM, gpUsername, gpPassword, gpPort, gpDatabase);
                    for (String action : actions.keySet()) {
                        if (!CollectionUtils.isEmpty(actions.get(action))) {
                            JSONObject typeJson = analysisConfigJson.getJSONObject(action);
                            String sql = typeJson.getString("sql");
                            List<String> needWhere = typeJson.getObject("where", List.class);
                            List<String> hasWhere = typeJson.containsKey("hasWhere") ? typeJson.getObject("hasWhere", List.class) : null;
                            List<String> orderBys = typeJson.containsKey("orderBy") ? typeJson.getObject("orderBy", List.class) : null;

                            String where = "";
                            if (!CollectionUtils.isEmpty(actions)) {
                                for (ParamDTO param : actions.get(action)) {
                                    if (needWhere.contains(param.getName())) {
                                        where += " and " + param.assembleWhere();
                                    }
                                }
                            }

                            String orderBy = ",";
                            if (!CollectionUtils.isEmpty(orderBys)) {
                                for (ParamDTO param : actions.get(action)) {
                                    if (orderBys.contains(param.getName())) {
                                        orderBy += param.getName();
                                    }
                                }
                            }
                            orderBy = orderBy.replaceFirst(",", "");

                            sql = sql.replaceAll("#\\{tableName}", gpSchema + "." + auditExceptionType.getTableName()).replaceAll("#\\{where}", where).replaceAll("#\\{orderBy}", orderBy);

                            if (!CollectionUtils.isEmpty(hasWhere)) {
                                for (ParamDTO param : actions.get(action)) {
                                    if (hasWhere.contains(param.getName())) {
                                        sql = sql.replaceAll("#\\{" + param.getName() + "}", param.getValue().toString());
                                    }
                                }
                            }
                            log.info("报表sql:{}", sql);

                            List<Map<String, Object>> statisticalReport = QueryHelper.selectMap(connection, sql);
                            result.put(action, statisticalReport);
                        }
                    }
                } catch (SQLException sqlException) {
                    sqlException.printStackTrace();
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                } finally {
                    if (connection != null) {
                        try {
                            connection.close();
                        } catch (SQLException sqlException) {
                            sqlException.printStackTrace();
                        }
                    }
                }
            }
        }
        return result;
    }

    /**
     * 获取异常统计列表
     *
     * @param date
     * @param cityId
     * @param typeId
     * @param specialId
     * @return
     */
    public PagerVO auditExceptionMonthVOList(String date, Integer cityId, Integer typeId, Integer specialId, Integer page, Integer pageSize) {
        int total = 0;
        String where = "";

        List<AuditExceptionMonthVO> auditExceptionMonthVOS = new ArrayList<>();
        if (cityId != null) {
            City city = cityMapper.selectById(cityId);
            where = " AND city_name = '" + city.getName().substring(0, 2) + "' ";
        }
        if (!StringUtils.isEmpty(date)) {
            where = " AND date = '" + date + "' ";
        }
        Connection connection = null;
        try {
            connection = DatabaseUtil.getConnection(gpIp, DataBaseType.GREENPLUM, gpUsername, gpPassword, gpPort, gpDatabase);
            if (typeId != null) {
                AuditExceptionSpecial special = auditExceptionSpecialService.getById(auditExceptionTypeService.get(typeId).getSpecialId());
                total += addAuditExceptionMonth(typeId, connection, pageSize, page, special.getName(), where, auditExceptionMonthVOS, 0);
            } else {
                //查询全部审计点
                List<AuditExceptionSpecial> list = auditExceptionSpecialService.list(new QueryWrapper<AuditExceptionSpecial>().eq(specialId != null, "intId", specialId));
                for (AuditExceptionSpecial simpleVO : list) {
                    List<AuditExceptionType> auditExceptionTypeList = auditExceptionTypeService.getList(simpleVO.getId());
                    for (AuditExceptionType auditExceptionType : auditExceptionTypeList) {
                        total += addAuditExceptionMonth(auditExceptionType.getId(), connection, pageSize, page, simpleVO.getName(), where, auditExceptionMonthVOS, null);
                    }
                }
                auditExceptionMonthVOS = auditExceptionMonthVOS.subList(auditExceptionMonthVOS.size() > (page - 1) * pageSize ? (page - 1) * pageSize : 0,
                        Math.min(auditExceptionMonthVOS.size(), (page - 1) * pageSize + pageSize));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException sqlException) {
                    sqlException.printStackTrace();
                }
            }
        }
        return PagerVO.of(page, pageSize, (long) total, auditExceptionMonthVOS);
    }

    public Integer addAuditExceptionMonth(Integer typeId, Connection connection, int pageSize, int page, String specialName, String where, List<AuditExceptionMonthVO> auditExceptionMonthVOS, Integer type) {
        int total = 0;
        AuditExceptionType auditExceptionType = auditExceptionTypeService.get(typeId);
        String sql = "SELECT city_name ,date ,count(*) total FROM " + gpSchema + "." + auditExceptionType.getTableName() + " WHERE 1=1 " + where + " GROUP BY date,city_name order by date DESC,total DESC ";
        log.info("异常统计Sql:{}", sql);
        try {
            List<Map<String, Object>> totalMap = QueryHelper.selectMap(connection, sql);
            total += totalMap.size();
            if (total > 0) {
                if (type != null) {
                    sql += " LIMIT " + pageSize + " OFFSET " + (page - 1) * pageSize;
                }
                List<Map<String, Object>> maps = QueryHelper.selectMap(connection, sql);
                for (Map<String, Object> map : maps) {
                    String cityName = map.get("city_name").toString() + "市";
                    City city = cityMapper.selectOne(new QueryWrapper<City>().eq("vcCity", cityName));
                    auditExceptionMonthVOS.add(AuditExceptionMonthVO.builder()
                            .cityId(city.getId().toString())
                            .cityName(cityName)
                            .date(map.get("date").toString())
                            .typeName(auditExceptionType.getName())
                            .specialId(auditExceptionType.getSpecialId())
                            .specialName(specialName)
                            .cnt(Integer.valueOf(map.get("total").toString()))
                            .build());
                }
            }
        } catch (SQLException e) {
            log.info(e.getMessage());
            e.printStackTrace();
        }
        return total;
    }

    /**
     * 异常审计报表统计
     */
    public void auditExceptionCountReport() {
        PagerVO<AuditExceptionSpecialSimpleVO> listSimple = auditExceptionSpecialService.listSimple(10025, 1, 10000);
        Connection connection = null;
        try {
            connection = DatabaseUtil.getConnection(gpIp, DataBaseType.GREENPLUM, gpUsername, gpPassword, gpPort, gpDatabase);
            List<AuditExceptionCountReport> list = new ArrayList<>();
            for (AuditExceptionSpecialSimpleVO simpleVO : listSimple.getRows()) {
                List<AuditExceptionType> types = auditExceptionTypeService.list(new QueryWrapper<AuditExceptionType>().eq("intSpecialId", simpleVO.getId()));
                for (AuditExceptionType auditExceptionType : types) {
                    String sql = "SELECT city_name ,date ,count(*) total FROM " + gpSchema + "." + auditExceptionType.getTableName() + " GROUP BY date,city_name order by date DESC,total DESC ";
                    log.info("=========统计sql:{}",sql);
                    List<Map<String, Object>> maps = null;
                    try {
                        maps = QueryHelper.selectMap(connection, sql);
                    } catch (SQLException e) {
                        e.printStackTrace();
                        log.info("=========异常sql:{}",sql);
                        log.info("=========异常信息:{}",e.getSQLState());
                    }
                    if (!CollectionUtils.isEmpty(maps)) {
                        for (Map<String, Object> map : maps) {
                            String cityName = map.get("city_name").toString() + "市";
                            City city = cityMapper.selectOne(new QueryWrapper<City>().eq("vcCity", cityName));
                            String countPeriods = DateUtil.parseDate2String(new Date(), "yyyy第MM期");
                            list.add(AuditExceptionCountReport.builder()
                                    .provinceId(city.getProvinceId())
                                    .cityId(city.getId())
                                    .cityName(cityName)
                                    .date(map.get("date").toString())
                                    .cnt(Integer.valueOf(map.get("total").toString()))
                                    .specialId(simpleVO.getId())
                                    .specialName(simpleVO.getName())
                                    .specialRisk(simpleVO.getImportance())
                                    .typeId(auditExceptionType.getId())
                                    .typeName(auditExceptionType.getName())
                                    .countPeriods(countPeriods)
                                    .build());
                        }
                    }
                }
            }
            auditExceptionCountReportService.saveBatch(list);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}
